package com.eleadmin.order.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.eleadmin.common.core.config.ConfigProperties;
import com.eleadmin.common.core.utils.FileServerUtil;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.DateUtil;
import com.eleadmin.damon.utils.utils.StringUtil;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.order.biz.*;
import com.eleadmin.order.entity.*;
import com.eleadmin.order.param.ContainerBillParam;
import com.eleadmin.order.param.OceanParam;
import com.eleadmin.order.service.OceanService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 海运服务实现类
 * @author : Doman
 * @date : 2023-04-24 11:26:09
 * @version ：1.0
 */
@Service
public class OceanServiceImpl /*extends ServiceImpl<OceanMapper, Ocean> */implements OceanService {

    @Autowired
    private OceanBiz oceanBiz;
    @Autowired
    private OceanFeeBiz oceanFeeBiz;
    @Autowired
    private ApplyCustomsFeeBiz applyCustomsFeeBiz;
    @Autowired
    private ContainerBillFeeBiz containerBillFeeBiz;
    @Autowired
    private CustomerBiz customerBiz;

    @Override
    public JsonResult list(PageResult2<T> page, Ocean ocean, User loginUser)  throws Exception{
        return oceanBiz.list(page,ocean,loginUser);
    }

    @Override
    public JsonResult list(OceanParam ocean, User loginUser) {
        return JsonResult.ok().setData(oceanBiz.list(ocean,loginUser));
    }

    @Override
    public JsonResult getById(String id) {
        return JsonResult.ok().setData(oceanBiz.getById(id));
    }

    @Transactional
    @Override
    public JsonResult save(Ocean ocean, User loginUser) {
        Customer customer = customerBiz.getById(ocean.getCustomerId()+"");
        ocean.setSalesmanid(customer.getSalesmanid());
        ocean.setSalesman(customer.getSalesman());
        ocean.setOperaterid(customer.getOperaterid());
        ocean.setOperater(customer.getOperater());
        if (oceanBiz.save(ocean, loginUser)) {
            return JsonResult.ok("添加成功");
        }
        return JsonResult.error("添加失败");
    }

    @Transactional
    @Override
    public JsonResult update(Ocean ocean, User loginUser) {
        if (oceanBiz.update(ocean, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult remove(String id, User loginUser) {
        if (oceanBiz.remove(id, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }





    //导出客户海运账单
    @Transactional
    @Override
    public void exportsOcean(Ocean ocean, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception{
        Ocean ocean2 = oceanBiz.getById2(ocean.getId());
        OceanFee oceanFee = new OceanFee();
        oceanFee.setOceanId(ocean2.getId());
        oceanFee.setType("应收");
        List<OceanFee> oceanFees = oceanFeeBiz.list(oceanFee, loginUser);


        List<ApplyCustomsFee> applyCustomsFees = null;
        if (Utils.isNotEmpty(ocean2.getApplyId())) {
            ApplyCustomsFee applyCustomsFee = new ApplyCustomsFee();
            applyCustomsFee.setApplyId(ocean2.getApplyId());
            applyCustomsFee.setType("应收");
            applyCustomsFees = applyCustomsFeeBiz.list(applyCustomsFee, loginUser);
        }

        List<ContainerBillFee> containerBillFees = null;
        if (Utils.isNotEmpty(ocean2.getApplyId())) {
            ContainerBillFee containerBillFee  =new ContainerBillFee();
            containerBillFee.setBillId(ocean2.getContainbillId());
            containerBillFee.setType("应收");
            containerBillFees = containerBillFeeBiz.list(containerBillFee, loginUser);
        }


        // 根据模板创建excel工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(getUploadDir() + "ocean_new.xlsx");

        // 获取创建的工作簿第一页
        XSSFSheet sheet = xssfWorkbook.getSheet("Sheet1");
        sheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建列，即单元格Cell
        XSSFRow xssfRow =  null;
        //创建列，即单元格Cell
        XSSFCell xssfCell = null;

        //把Excel模板文件里的数据清空
        setBlankExcel(xssfWorkbook, sheet,oceanFees, applyCustomsFees, containerBillFees);
        // 获取第一行
        xssfRow = sheet.getRow(4);

        // 先定义创建excel表头
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("To:" + ocean2.getCustomer());

        xssfRow = sheet.getRow(6);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("提单号:" + ocean2.getBillOfLading());
        xssfRow = sheet.getRow(7);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("船名航次:" + ocean2.getVessel()+"/ "+ocean2.getVoyage());
        xssfRow = sheet.getRow(8);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("柜型:" + ocean2.getBoxType());

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(false); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


        int count = 11;
        OceanFee oceanFee2 = null;
        float sum = 0, usd = 0, cny = 0, myr = 0;
        if (oceanFees != null && oceanFees.size() > 0) {
            count += oceanFees.size();
            for (int i = 0; i < oceanFees.size(); i ++) {
                oceanFee2 = oceanFees.get(i);
                xssfRow = sheet.getRow(11+i);
                xssfCell = xssfRow.getCell(0); //创建单元格
                xssfCell.setCellValue(oceanFee2.getFeeName());
                xssfCell = xssfRow.getCell(1); //创建单元格
                xssfCell.setCellValue(oceanFee2.getPrice());
                xssfCell = xssfRow.getCell(2); //创建单元格
                xssfCell.setCellValue(1);
                try {
                    if (oceanFee2.getCurrency().equals("USD")) {
                        xssfCell = xssfRow.getCell(3); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(oceanFee2.getPrice()));
                        usd += Double.valueOf(oceanFee2.getPrice());
                    }
                    if (oceanFee2.getCurrency().equals("CNY")) {
                        xssfCell = xssfRow.getCell(4); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(oceanFee2.getPrice()));
                        cny += Double.valueOf(oceanFee2.getPrice());
                    }
                    if (oceanFee2.getCurrency().equals("MYR")) {
                        xssfCell = xssfRow.getCell(5); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(oceanFee2.getPrice()));
                        myr += Double.valueOf(oceanFee2.getPrice());
                    }
                    sum += Double.valueOf(oceanFee2.getPrice());
                } catch (Exception e){

                }
                xssfCell = xssfRow.getCell(6); //创建单元格
                xssfCell.setCellValue(oceanFee2.getRemark());
            }
        }

        ApplyCustomsFee applyCustomsFee2 = null;
        if (applyCustomsFees != null && applyCustomsFees.size() > 0) {
            count += applyCustomsFees.size();
            for (int i = 0; i < applyCustomsFees.size(); i ++) {
                applyCustomsFee2 = applyCustomsFees.get(i);
                xssfRow = sheet.getRow(11 + i + oceanFees.size());
                xssfCell = xssfRow.getCell(0); //创建单元格
                xssfCell.setCellValue(applyCustomsFee2.getFeeName());
                xssfCell = xssfRow.getCell(1); //创建单元格
                xssfCell.setCellValue(applyCustomsFee2.getPrice());
                xssfCell = xssfRow.getCell(2); //创建单元格
                xssfCell.setCellValue(1);
                try {
                    if (applyCustomsFee2.getCurrency().equals("USD")) {
                        xssfCell = xssfRow.getCell(3); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(applyCustomsFee2.getPrice()));
                        usd += Double.valueOf(applyCustomsFee2.getPrice());
                    }
                    if (applyCustomsFee2.getCurrency().equals("CNY")) {
                        xssfCell = xssfRow.getCell(4); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(applyCustomsFee2.getPrice()));
                        cny += Double.valueOf(applyCustomsFee2.getPrice());
                    }
                    if (applyCustomsFee2.getCurrency().equals("MYR")) {
                        xssfCell = xssfRow.getCell(5); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(applyCustomsFee2.getPrice()));
                        myr += Double.valueOf(applyCustomsFee2.getPrice());
                    }
                    sum += Double.valueOf(applyCustomsFee2.getPrice());
                } catch (Exception e){

                }
                xssfCell = xssfRow.getCell(6); //创建单元格
                xssfCell.setCellValue(applyCustomsFee2.getRemark());
            }
        }

        ContainerBillFee containerBillFee2 = null;
        if (containerBillFees != null && containerBillFees.size() > 0) {
            count += containerBillFees.size();
            for (int i = 0; i < containerBillFees.size(); i ++) {
                containerBillFee2 = containerBillFees.get(i);
                xssfRow = sheet.getRow(11 + i +oceanFees.size() + applyCustomsFees.size());
                xssfCell = xssfRow.getCell(0); //创建单元格
                xssfCell.setCellValue(containerBillFee2.getFeeName());
                xssfCell = xssfRow.getCell(1); //创建单元格
                xssfCell.setCellType(CellType.NUMERIC);
                xssfCell.setCellValue(Double.valueOf(containerBillFee2.getPrice()));
                xssfCell = xssfRow.getCell(2); //创建单元格
                xssfCell.setCellValue(1);
                try {
                    if (containerBillFee2.getCurrency().equals("USD")) {
                        xssfCell = xssfRow.getCell(3); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(containerBillFee2.getPrice()));
                        usd += Double.valueOf(containerBillFee2.getPrice());
                    }
                    if (containerBillFee2.getCurrency().equals("CNY")) {
                        xssfCell = xssfRow.getCell(4); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(containerBillFee2.getPrice()));
                        cny += Double.valueOf(containerBillFee2.getPrice());
                    }
                    if (containerBillFee2.getCurrency().equals("MYR")) {
                        xssfCell = xssfRow.getCell(5); //创建单元格
                        xssfCell.setCellType(CellType.NUMERIC);
                        xssfCell.setCellValue(Double.valueOf(containerBillFee2.getPrice()));
                        myr += Double.valueOf(containerBillFee2.getPrice());
                    }
                    sum += Double.valueOf(containerBillFee2.getPrice());
                } catch (Exception e){

                }
                xssfCell = xssfRow.getCell(6); //创建单元格
                xssfCell.setCellValue(containerBillFee2.getRemark());
            }
        }

//        xssfRow = sheet.getRow(27);
//        xssfCell = xssfRow.getCell(0); //创建单元格
//        xssfCell.setCellValue("合计:"+ sum);

        fontStyle.setBold(true); // 加粗
        cellStyle.setFont(fontStyle);
        xssfRow = sheet.getRow(count);
        xssfRow.setHeightInPoints(30); // 设置行高为30个点
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("应收USD:"+ String.valueOf(usd).replaceAll("\\.0*$", ""));
        xssfCell = xssfRow.getCell(1); //创建单元格
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("应收MYR:"+ String.valueOf(myr).replaceAll("\\.0*$", ""));
        xssfCell = xssfRow.getCell(6); //创建单元格
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("应收RMB:"+ String.valueOf(cny).replaceAll("\\.0*$", ""));

        xssfRow = sheet.getRow(count+1);
        xssfRow.setHeightInPoints(80); // 设置行高为30个点
        xssfCell = xssfRow.getCell(0); //创建单元格
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("以上为贵司代理运输所产生的费用，请在2天内确认完毕，逾期视作默认，并3天内付清费用，感谢配合。");

        xssfRow = sheet.getRow(count+2);
        xssfRow.setHeightInPoints(300); // 设置行高为30个点
        xssfCell = xssfRow.getCell(0); //创建单元格
        cellStyle.setWrapText(true);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("个人账号：\n" +
                "户名：蔡寒峰\n" +
                "账号：6228480318392094476\n" +
                "开户行：中国农业银行股份有限公司宁波柴桥支行\n" +
                "\n" +
                "公司账号：\n" +
                "账户名：宁波甩手掌柜供应链有限公司\n" +
                "税号：91330201MA2H47KE90\n" +
                "开户行：中国农业银行股份有限公司宁波潘火支行\n" +
                "账号：(RMB)  39446001040006801\n" +
                "开户行：宁波银行保险科技产业园支行\n" +
                "银行账号：(RMB)3206 0122 0000 1471 2\n" +
                "银行账号：（USD）3206 2025 0000 0012 6\n" +
                "SWIFT CODE：BKNBCN2NXXX\n" +
                "发票寄到浙江省宁波市鄞州区 下应街道 宁波保险科技产业园 甩手掌柜2201-3，黄小姐 18892631673");


        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(count+1, count+1, 0, 0 + 6); // 起始行, 终止行, 起始列, 终止列
        sheet.addMergedRegion(cra);
        cra = new CellRangeAddress(count+2, count+2, 0, 0 + 6); // 起始行, 终止行, 起始列, 终止列
        sheet.addMergedRegion(cra);

        try {

            // 创建Excel文件的输入流对象
            OutputStream os = response.getOutputStream();
            // 这时候把创建好的excel写入到输出流
            xssfWorkbook.write(os);
            // 关闭流
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            response.setContentType("text/html; charset=UTF-8");
            response.getWriter().println("没有对应的数据哦,请再次查询!");
        }
    }

    public void setBlankExcel(XSSFWorkbook xssfWorkbook, XSSFSheet sheet,
                              List<OceanFee> oceanFees, List<ApplyCustomsFee> applyCustomsFees, List<ContainerBillFee> containerBillFees){
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(false); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        int count = 0;
        if (Utils.isNotEmpty(oceanFees)) {
            count += oceanFees.size();
        }
        if (Utils.isNotEmpty(applyCustomsFees)) {
            count += applyCustomsFees.size();
        }
        if (Utils.isNotEmpty(containerBillFees)) {
            count += containerBillFees.size();
        }
        XSSFRow xssfRow = null;
        XSSFCell xssfCell = null;
        for (int i = 11; i < 11+ count; i++) {
            xssfRow = sheet.getRow(i);
            xssfCell = xssfRow.getCell(0); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(1); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(2); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(3); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(4); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(5); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(6); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
        }

    }



    //应收应付分类导出财务报表
    @Override
    public JsonResult exportsFinance(Ocean ocean, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        String fileName = "finance.xlsx";
        File file = new File(getUploadDir(), fileName);
        if (file.exists()) {
            file.delete();
        }
        ocean.setDate1(ocean.getDate1().substring(0, 10) + " 00:00");
        ocean.setDate2(ocean.getDate2().substring(0, 10) + " 23:59");
        List<Ocean> oceanList = getOceanList(ocean, loginUser);
        if (oceanList != null && oceanList.size() > 0) {
            List<String> feeNames = oceanFeeBiz.feeNames(ocean, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsFinanceOceanExcel(oceanList, ocean, fileName);
                FileServerUtil.preview(file, true, fileName, null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }


    public List<Ocean> getOceanList(Ocean ocean, User loginUser) {
        OceanParam oceanParam = new OceanParam();
        oceanParam.setFactoryPackDateSearch1(ocean.getDate1());
        oceanParam.setFactoryPackDateSearch2(ocean.getDate2());
        oceanParam.setCustomerEq(ocean.getCustomer());
        oceanParam.setBookingAgent(ocean.getBookingAgent());
        List<Ocean> oceanList = oceanBiz.list(oceanParam, loginUser);
        if (oceanList != null && oceanList.size() > 0) {
            for (int i = 0; i < oceanList.size(); i++) {
                Ocean oceanTemp = oceanList.get(i);
                oceanParam = new OceanParam();
//                if (StringUtil.isNotBlank(containerBill.getType())) {
//                    oceanParam.setType(containerBill.getType());//导出应收应付
//                }
                if (StringUtil.isNotBlank(oceanTemp.getId())) {
                    oceanParam.setId(oceanTemp.getId());
                }
//                if (StringUtil.isNotBlank(oceanTemp.getApplyId())) {
//                    oceanParam.setApplyId(oceanTemp.getApplyId());
//                }
//                if (StringUtil.isNotBlank(containerBill.getCustomerAbbreviation())) {
//                    oceanParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
//                }
                List<OceanFee> oceanFeeList = oceanFeeBiz.listForExport(oceanParam, loginUser);
                oceanTemp.setOceanFeeList(oceanFeeList);
            }
        }
        return oceanList;
    }

    public void exportsFinanceOceanExcel(List<Ocean> oceanList, Ocean ocean, String fileName) throws Exception {
        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(ocean.getDate2())) {
            date2 = " 到 " + ocean.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + ocean.getDate1().substring(0, 10) + date2); //设置单元格内容

        List<String> feeNamesys = new ArrayList<>();
        feeNamesys.add("运费");
        feeNamesys.add("提箱");
        feeNamesys.add("进港");
        feeNamesys.add("其他");
        feeNamesys.add("单项");
        feeNamesys.add("采购");
        feeNamesys.add("报关费");
        feeNamesys.add("易港通");
        feeNamesys.add("财务报销");
        List<String> feeNamesyf = new ArrayList<>();
        feeNamesyf.add("运费");
        feeNamesyf.add("提箱");
        feeNamesyf.add("进港");
        feeNamesyf.add("其他");
        feeNamesyf.add("单项");
        feeNamesyf.add("采购");
        feeNamesyf.add("报关费");
        feeNamesyf.add("易港通");
        feeNamesyf.add("财务报销");

        List<String> headRow = CollUtil.newArrayList("序号", "开船日期", "客户简称", "船公司", "订舱代理", "船名", "航次", "提单号",
                "起运港", "目的港", "操作员", "业务员");
        headRow.add("应收明细");
        headRow.addAll(feeNamesys);
        headRow.remove(headRow.size() - 1);
        headRow.add("应收合计");
        headRow.add("应付明细");
        headRow.addAll(feeNamesyf);
        headRow.remove(headRow.size() - 1);
        headRow.add("应付合计");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

        headRow = CollUtil.newArrayList("序号", "开船日期", "客户简称", "船公司", "订舱代理", "船名", "航次", "提单号",
                "起运港", "目的港", "操作员", "业务员");
        headRow.addAll(feeNamesys);
        headRow.add("应收合计");
        headRow.addAll(feeNamesyf);
        headRow.add("应付合计");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(4);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

        String[] letters = new String[]{ "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB",
                "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT",
                "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL",
                "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ"};

        //把List里面的数据写到excel中
        for (int i = 0; i < oceanList.size(); i++) {
            Ocean ocean2 = oceanList.get(i);
            List<OceanFee> oceanFees = ocean2.getOceanFeeList();

            xssfRow = xssfSheet.createRow(i + 5);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i + 1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(ocean2.getCustomer()); //设置单元格内容

            List<String> row = CollUtil.newArrayList((i + 1) + "", DateUtil.formatDate(DateUtil.parseDate(ocean2.getSailingDate() + ":00"), "MM-dd"),
                    ocean2.getCustomer(), ocean2.getShippingCompany(), ocean2.getBookingAgent(),
                    ocean2.getVoyage(), ocean2.getVessel(), ocean2.getBillOfLading(),
                    ocean2.getPortOfDeparture() == null ? "/" : ocean2.getPortOfDeparture(), ocean2.getPortOfDeparture() == null ? "/" : ocean2.getPortOfDeparture(),
                    ocean2.getOperater(), ocean2.getSalesman()
            );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            feeNamesys.stream().forEach(name->{
                float temp_ret =oceanFees.stream().map(bf -> bf.ysPirce("应收", name))
                        .reduce(Float.intBitsToFloat(0), (n1,n2) ->n1+n2 );
                row.add(temp_ret + "");
            });

            row.add("function::SUM(" + letters[0] + (i + 6) + ":" + letters[feeNamesys.size() + -1] + (i + 6) + ")");
            feeNamesyf.stream().forEach(feeName->{
                float temp_ret =oceanFees.stream().map(bf -> bf.ysPirce("应付", feeName))
                        .reduce(Float.intBitsToFloat(0), (n1,n2) ->n1+n2 );
                row.add(temp_ret + "");
            });

            row.add("function::SUM(" + letters[feeNamesys.size() + 1] + (i + 6) + ":" + letters[feeNamesys.size() + feeNamesyf.size() + 0] + (i + 6) + ")");

            int profitIndex = feeNamesys.size() + feeNamesyf.size() +1;
            row.add("function::TRUNC(IMSUB(" + letters[feeNamesys.size()] + (i + 6) + "," + letters[profitIndex] + (i + 6) + "), 2)");
            row.add("");
            String numColumns = ",";
            for (int t = 0; t < feeNamesys.size() + feeNamesys.size() + 2; t++) {
                numColumns += (t + 12) + ",";
            }
            setRow(xssfRow, row, cellStyle, numColumns);
        }

        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 15 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(1, 1, 0, 15 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 0, 15 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(3, 3, 12, 11 + feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(3, 3, 13 + feeNamesys.size(), 12 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        for (int i = 0; i <= 15 + feeNamesys.size() + feeNamesyf.size(); i++) {
            if (i < 12 || i == (12 + feeNamesys.size()) || i > (12 + feeNamesys.size() + feeNamesyf.size())) {
                cra = new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
                xssfSheet.addMergedRegion(cra);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
            }
        }

//        xssfRow = xssfSheet.createRow(5 + oceanList.size());
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("合计:"); //设置单元格内容
//
//        for (int i = 0; i <= feeNamesys.size() + feeNamesyf.size() + 6; i++) {
//            xssfCell = xssfRow.createCell(18 + i); //创建单元格
//            fontStyle = xssfWorkbook.createFont(); // 字体样式
//            fontStyle.setFontName("宋体"); // 字体
//            fontStyle.setFontHeightInPoints((short) 10); // 大小
//            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            // 将字体样式添加到单元格样式中
//            cellStyle.setFont(fontStyle);
//            cellStyle.setBorderBottom(BorderStyle.THIN);
//            cellStyle.setBorderLeft(BorderStyle.THIN);
//            cellStyle.setBorderRight(BorderStyle.THIN);
//            cellStyle.setBorderTop(BorderStyle.THIN);
//            xssfCell.setCellStyle(cellStyle);
//            xssfCell.setCellType(CellType.NUMERIC);
//            xssfCell.setCellFormula("SUM(" + letters[i + 8] + "6:" + letters[i + 8] + (oceanList.size() + 5) + ")"); //设置单元格内容
//        }
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(20 + feeNamesys.size() + feeNamesyf.size() + 5); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue(""); //设置单元格内容
//
//        cra = new CellRangeAddress(5 + oceanList.size(), 5 + oceanList.size(), 0, 17); // 起始行, 终止行, 起始列, 终止列
//        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
//        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + fileName));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }

    public void setRow(XSSFRow xssfRow, List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if (str == null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if (str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10, str.length())); //设置单元格内容
                } else if (changeNum.indexOf("," + i + ",") != -1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if (Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }

    @Resource
    private ConfigProperties config;

    /**
     * 文件上传基目录
     */
    private String getUploadBaseDir() {
        return File.listRoots()[config.getUploadLocation()].getAbsolutePath()
                .replace("\\", "/") + "/upload/";
    }

    /**
     * 文件上传位置
     */
    private String getUploadDir() {
        return getUploadBaseDir() + "file/";
    }
}
